var io = require('socket.io').listen(7070);
var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '31824169',
    database : 'entrenate'
});

connection.connect();

io.sockets.on('connection', function (socket) {

    var socketRef = socket;

    socket.on('horarioPopularRequest', function (user){
        var out = "";
        connection.query(
"        	select " +
"        		GROUP_CONCAT(entradas ORDER BY horaEjercitadas SEPARATOR ',') AS entradas," +
"        		GROUP_CONCAT(horaEjercitadas ORDER BY horaEjercitadas SEPARATOR ',') AS horaEjercitadas" +
 "        	from(" +
 "        	select " +
 "        		count(*) as entradas," +
 "        		date_format( time, '%H' ) as horaEjercitadas" +
 "        	from entrenate.usuario_actividad_with_sensor" +
 "        	inner join horasDia" +
 "        		on	date_format( time, '%H' ) = horasDia.hora" +
 " 			where user_id = "+connection.escape(user)+"" +
 "        	group by horaEjercitadas" +
 "        	UNION" +
 "        	select " +
 "        		0 as entradas," +
 "        		horasDia.hora as horaEjercitadas" +
 "        	from horasDia" +
 "			where horasDia.hora not in (" +
 "					select date_format( time, '%H' ) from entrenate.usuario_actividad_with_sensor" +
 " 			where user_id = "+connection.escape(user)+"" +					
 "					)" +  
 "        	) horasEntrenando" +
 "        	order by horaEjercitadas",
        function(err, rows, fields) {
            if (err) throw err;
            out = JSON.stringify(rows[0]);
            socketRef.emit('horarioPopularData', out);
            console.log('sent popular hours data');
        });
    });
    
    socket.on('tiposDeportesRequest', function (user){
        var out = "";
        connection.query(
 "       	select" +
 "        		GROUP_CONCAT(entradas ORDER BY nombre SEPARATOR ',') AS entradas," +
 "        		GROUP_CONCAT(nombre ORDER BY nombre SEPARATOR ',') AS nombre" +
 "       	from(" +
 "       	select " +
 "       		count(*) as entradas," +
 "       		deporte.nombre" +
 "       	from entrenate.usuario_actividad_with_sensor as sensor" +
 "       	inner join deporte" +
 "       		on	deporte.id = sensor.deporte_id" +
 " 			where user_id = "+connection.escape(user) +					
 "       	group by deporte.id" +
 "       	UNION" +
 "       	select " +
 "       		0 as entradas," +
 "       		deporte.nombre" +
 "       	from deporte" +
 "			where deporte.id not in (" +
 "			select deporte_id from entrenate.usuario_actividad_with_sensor" +
 " 			where user_id = "+connection.escape(user)+"" +
 "				)" +
 "		) deportesEntrenando"+
 "		order by nombre ASC",
        function(err, rows, fields) {
            if (err) throw err;
            out = JSON.stringify(rows[0]);
            socketRef.emit('tiposDeportesData', out);
            console.log('sent deportes por usuario data');
        });
    });
    
    socket.on('caloriasDeportesRequest', function (user){
        var out = "";
        connection.query(
 "       	select" +
 "        		GROUP_CONCAT(calorias ORDER BY time SEPARATOR ',') AS calorias," +
 "        		GROUP_CONCAT(time ORDER BY time SEPARATOR ',') AS time" +
 "       	from calorias_por_mes" +
 " 			where user_id = "+connection.escape(user),
 		function(err, rows, fields) {
            if (err) throw err;
            out = JSON.stringify(rows[0]);
            socketRef.emit('caloriasMensualesData', out);
            console.log('sent calorias por mes por usuario data');
        });
    });
    
    socket.on('caloriasMensualesRequest', function (user){
        var out = "";
        connection.query(
 "       	select" +
 "        		GROUP_CONCAT(calorias ORDER BY nombre SEPARATOR ',') AS calorias," +
 "        		GROUP_CONCAT(nombre ORDER BY nombre SEPARATOR ',') AS nombre" +
 "       	from(" +
 "       	select " +
 "       		(count(*)/60) * deporte.caloriasHora as calorias," +
 "       		deporte.nombre" +
 "       	from entrenate.usuario_actividad_with_sensor as sensor" +
 "       	inner join deporte" +
 "       		on	deporte.id = sensor.deporte_id" +
 " 			where user_id = "+connection.escape(user) +					
 "       	group by deporte.id" +
 "       	UNION" +
 "       	select " +
 "       		0 as calorias," +
 "       		deporte.nombre" +
 "       	from deporte" +
 "			where deporte.id not in (" +
 "			select deporte_id from entrenate.usuario_actividad_with_sensor" +
 " 			where user_id = "+connection.escape(user)+"" +
 "				)" +
 "		) deportesEntrenando"+
 "		order by nombre ASC",
        function(err, rows, fields) {
            if (err) throw err;
            out = JSON.stringify(rows[0]);
            socketRef.emit('caloriasDeportesData', out);
            console.log('sent calorias por deportes por usuario data');
        });
    });
    

});

process.on('SIGINT', function() {
    console.log( "\nGracefully shutting down from SIGINT (Ctrl-C)");
    connection.end();
    process.exit();
})
